-- @owner: @wang-tianjing1
-- @date: 2022-03-23
-- @testpoint: start with connect by 结合record子查询识别

--step1: 创建查询表 expect: 创建成功
drop table if exists t_connect_0051_01;
drop table if exists t_connect_0051_02;
create table t_connect_0051_01 (id int,pid int);
create table t_connect_0051_02 (id int,pid int);

--step2: 查询表插入数据 expect: 成功
insert into t_connect_0051_01 values(141,131),(131,121),(121,111),(111,11),(11,1),(1,0);
insert into t_connect_0051_02 values(141,131),(131,121),(121,111),(111,11),(11,1),(1,0);

--step3: connect by语句结合where条件; expect: 查询成功
select t1.id,t1.pid,t2.id,t2.pid from t_connect_0051_01 t1 join t_connect_0051_02 t2 on t1.id=t2.id where row(1,0)>=(
    select id,pid from t_connect_0051_02 start with id=1 connect by prior id=pid and level=1)
start with t1.id=141 connect by prior t2.pid=t1.id;

--step4: 清理环境 expect: 成功
drop table if exists t_connect_0051_01;
drop table if exists t_connect_0051_02;